package com.jycrm.service.serve;

public class DzdSql {
	public static final String STARTDATE = ":startdate";
	public static final String ENDDATE =":enddate";
	public static final String CURRSHARES = ":currshares";
	
	
    public static String MONTH_RECKON_UP = 
    	
    	" ( select taaccountid from (select a.taaccountid,a.fundcode,sum(currentshares)from crm_increment_share a " +
    	" where a.startdate <= "+ENDDATE+" and a.enddate >= "+ENDDATE+" and   a.currentshares > 0 group by a.taaccountid,a.fundcode " +
    	" having sum(currentshares) >= "+CURRSHARES+" ) union select b.taaccountid from crm_cust_confirm b where b.transactioncfmdate >= "+STARTDATE +
    	" and b.transactioncfmdate <= "+ENDDATE+" union select c.taaccountid from crm_cust_dividenddetail c where c.dividentdate >= "+STARTDATE +
    	" and c.dividentdate <= "+ENDDATE ;
    
    public static String MONTH_RECKON_DOWN =
    	
    	"select b.taaccountid from crm_increment_share b where b.startdate <= "+ENDDATE+" and b.enddate >= "+ENDDATE+" and b.currentshares > 0 " +
    	" union select b.taaccountid from crm_cust_confirm b where b.transactioncfmdate >= "+STARTDATE+" and b.transactioncfmdate <= "+ENDDATE +
    	" union select c.taaccountid from crm_cust_dividenddetail c where c.dividentdate >= "+STARTDATE+" and c.dividentdate <= "+ENDDATE ;
    
    public static String MONTH_SCRIP_RECKON_UP =
    	
    	"select taaccountid from ( select a.taaccountid,a.fundcode,sum(currentshares) from crm_increment_share a " +
    	" where a.startdate <= "+ENDDATE+" and a.enddate >= "+ENDDATE+" and   a.currentshares > 0 group by a.taaccountid,a.fundcode " +
    	" having sum(currentshares) >= "+CURRSHARES+" ) union select b.taaccountid from crm_cust_confirm b where b.transactioncfmdate >= "+STARTDATE+
    	" and b.transactioncfmdate <= "+ENDDATE+" and b.businesscode not in ('120','130')  union select c.taaccountid from crm_cust_dividenddetail c " +
    	" where c.dividentdate >= "+STARTDATE+" and   c.dividentdate <= "+ENDDATE ;
    
    public static String MONTH_SCRIP_RECKON_DOWN = 
    	
    	"select taaccountid from ( select a.taaccountid,a.fundcode,sum(currentshares) from crm_increment_share a where a.startdate <= "+ENDDATE +
    	" and a.enddate >= "+ENDDATE+" and a.currentshares > 0 group by a.taaccountid,a.fundcode having sum(currentshares) >= "+CURRSHARES+" ) " +
    	" union select b.taaccountid from crm_increment_share b where b.startdate <= "+ENDDATE+" and b.enddate >= "+ENDDATE+" and b.currentshares > 0 " +
    	" and not exists ( select 1 from crm_cust_confirm c where c.transactioncfmdate >= "+STARTDATE+" and c.transactioncfmdate <= "+ENDDATE+
    	" and c.businesscode in ('120','130') and   c.taaccountid = b.taaccountid and c.fundcode = b.fundcode ) union select b.taaccountid " +
    	" from crm_cust_confirm b where b.transactioncfmdate >= "+STARTDATE+" and b.transactioncfmdate <= "+ENDDATE+" and b.businesscode not in ('120','130') " +
    	" union select c.taaccountid from crm_cust_dividenddetail c where c.dividentdate >= "+STARTDATE+" and   c.dividentdate <= "+ENDDATE ;
    
    public static String SCRIP_RECKON_UP =
    	"select distinct a.taaccountid from crm_cust_confirm a where a.transactioncfmdate = "+ENDDATE+" and a.businesscode = '130'";
}
